<?php

class m_property extends Zmodel{
    function __construct() {
   	    parent::__construct();
    }
    
    function get_properties($num,$offset) {
    	$this->load->database();
    	if($offset!=""){
    		$sql = "SELECT DISTINCT property.property_id,
							property.property_name,
							(SELECT location_name FROM loc_property loc WHERE loc.location_id = property.location_id  LIMIT 1) as location_name,
							property.property_type,
							property.no_of_floor,
							property.no_of_bedrooms,
							property.features,
							property.description,
							property.property_price,
							property.b_size,
							property.date_create,
							property.land_size,
							property.file_path,
							(SELECT users.user_login FROM users WHERE users.user_id = CAST(property.user_upload as int) LIMIT 1) as user_login,
							property.parking,
						    (SELECT pic_name FROM pic_id_property pi WHERE pi.property_id = property.property_id  LIMIT 1) as pic_name,
							property.no_of_bathroom
				FROM property
				ORDER BY
						property.date_create DESC LIMIT ".$num." OFFSET ".$offset."";
    	}else {
    		   $sql = "SELECT DISTINCT property.property_id,
							property.property_name,
							(SELECT location_name FROM loc_property loc WHERE loc.location_id = property.location_id  LIMIT 1) as location_name,
							property.property_type,
							property.no_of_floor,
							property.no_of_bedrooms,
							property.features,
							property.description,
							property.property_price,
							property.b_size,
							property.date_create,
							property.land_size,
							property.file_path,
							(SELECT users.user_login FROM users WHERE users.user_id = CAST(property.user_upload as int) LIMIT 1) as user_login,
							property.parking,
						    (SELECT pic_name FROM pic_id_property pi WHERE pi.property_id = property.property_id  LIMIT 1) as pic_name,
							property.no_of_bathroom
				FROM property
				ORDER BY
						property.date_create DESC LIMIT ".$num." OFFSET 0";
    	}
        
        return $this->db->query($sql);
        //return $query;
       	//$nb_rows = $query->num_rows();
        //if ($nb_rows < 1) {
        //    return NULL;
        //} else {
        //    return $query->row_array();
        //}
    }
    function get_property() {
    	$this->load->database();
    		$sql = "SELECT DISTINCT property.property_id,
							property.property_name,
							(SELECT location_name FROM loc_property loc WHERE loc.location_id = property.location_id  LIMIT 1) as location_name,
							property.property_type,
							property.no_of_floor,
							property.no_of_bedrooms,
							property.features,
							property.description,
							property.property_price,
							property.b_size,
							property.date_create,
							property.land_size,
							property.file_path,
							(SELECT users.user_login FROM users WHERE users.user_id = CAST(property.user_upload as int) LIMIT 1) as user_login,
							property.parking,
						    (SELECT pic_name FROM pic_id_property pi WHERE pi.property_id = property.property_id  LIMIT 1) as pic_name,
							property.no_of_bathroom
				FROM property
				ORDER BY property.date_create DESC";
    		return $this->db->query($sql);
    }
    
    function get_property_by_user() {
    	$this->load->database();
    	$Z_USER = Zsession::getUserFromSession();
	    $users=$Z_USER["user_id"];
    		$sql = "SELECT DISTINCT property.property_id,
							property.property_name,
							(SELECT location_name FROM loc_property loc WHERE loc.location_id = property.location_id  LIMIT 1) as location_name,
							property.property_type,
							property.no_of_floor,
							property.no_of_bedrooms,
							property.features,
							property.description,
							property.property_price,
							property.b_size,
							property.date_create,
							property.land_size,
							property.file_path,
							property.user_upload,
							property.parking,
						    (SELECT pic_name FROM pic_id_property pi WHERE pi.property_id = property.property_id  LIMIT 1) as pic_name,
							property.no_of_bathroom
				FROM property
				WHERE 	user_upload='".$users."'		
				ORDER BY property.date_create DESC";
    		return $this->db->query($sql);
    }
    
    function get_property_main() {
    	$this->load->database();
    		$sql = "SELECT DISTINCT property.property_id,
							property.property_name,
							(SELECT location_name FROM loc_property loc WHERE loc.location_id = property.location_id  LIMIT 1) as location_name,
							property.property_type,
							property.no_of_floor,
							property.no_of_bedrooms,
							property.features,
							property.description,
							property.property_price,
							property.b_size,
							property.date_create,
							property.land_size,
							property.file_path,
							(SELECT users.user_login FROM users WHERE users.user_id = CAST(property.user_upload as int) LIMIT 1) as user_login,
							property.parking,
						    (SELECT pic_name FROM pic_id_property pi WHERE pi.property_id = property.property_id  LIMIT 1) as pic_name,
							property.no_of_bathroom
				FROM property
				ORDER BY	 property.date_create DESC LIMIT 5";
    		return $this->db->query($sql);
    }
    
    function insert_properties($property_name,
					  $propety_location_id,
					  $propety_type,
					  $no_of_floor,
					  $no_of_bedrooms,
					  $features,
					  $description,
					  $property_price,
					  $b_size,
					  $land_size,
					  $file_path,
					  $no_of_bathroom,
					  $user_upload,
					  $parking,
					  $property_cat) {
    	$this->load->database();
    	$prop_id = $this->get_next_id("property_property_id_seq");
    	$property_cat = $property_cat == "" ? "null" : $property_cat;
    	$sql = "INSERT INTO property(
    	              property_id,
    	              property_name,
					  location_id,
					  property_type,
					  no_of_floor,
					  no_of_bedrooms,
					  features,
					  description,
					  property_price,
					  b_size,
					  land_size,
					  file_path,
					  no_of_bathroom,
					  date_create,
					  user_upload,
					  parking,
					  property_cat)
				VALUES(".$prop_id.",
				      '".pg_escape_string($property_name)."',
					  '".pg_escape_string($propety_location_id)."',
					  '".pg_escape_string($propety_type)."',
					  '".pg_escape_string($no_of_floor)."',
					  '".pg_escape_string($no_of_bedrooms)."',
					  '".pg_escape_string($features)."',
					  '".pg_escape_string($description)."',
					  '".pg_escape_string($property_price)."',
					  '".pg_escape_string($b_size)."',
					  '".pg_escape_string($land_size)."',
					  '".pg_escape_string($file_path)."',
					  '".pg_escape_string($no_of_bathroom)."',
    				  CURRENT_TIMESTAMP,
    				  ".$user_upload.",
    				  '".pg_escape_string($parking)."',
    				  ".pg_escape_string($property_cat).")";	 
    	$this->db->query($sql);
    	return $prop_id;
    }
    
   	function renew($id){
		$sql ="SET TIMEZONE TO 'Asia/Phnom_Penh';
			   UPDATE property SET date_create = CURRENT_TIMESTAMP WHERE property_id=".$id;
		$this->db->query($sql);
	}
	
	
	function update_properties($id,
					  $property_name,
					  $location_id,
					  $propety_type,
					  $no_of_floor,
					  $no_of_bedrooms,
					  $features,
					  $description,
					  $property_price,
					  $b_size,
					  $land_size,
					  $pic,
					  $file_path,
					  $no_of_bathroom,
					  $parking,
					  $property_cat) {
    	//$this->load->database();
    	
		$property_cat = $property_cat == "" ? "null" : $property_cat;
    	$sql = "UPDATE property	  
				SET   property_name='".pg_escape_string($property_name)."',
					  location_id='".pg_escape_string($location_id)."',
					  property_type='".pg_escape_string($propety_type)."',
					  no_of_floor='".pg_escape_string($no_of_floor)."',
					  no_of_bedrooms='".pg_escape_string($no_of_bedrooms)."',
					  features='".pg_escape_string($features)."',
					  description='".pg_escape_string($description)."',
					  property_price='".pg_escape_string($property_price)."',
					  b_size='".pg_escape_string($b_size)."',
					  land_size='".pg_escape_string($land_size)."',
					  pic_id='".pg_escape_string($id)."',
					  file_path='".pg_escape_string($file_path)."',
					  no_of_bathroom='".pg_escape_string($no_of_bathroom)."',
    				  date_update = CURRENT_TIMESTAMP,
    				  parking='".pg_escape_string($parking)."',
    				  property_cat=".$property_cat."
    			WHERE property_id=".$id;	 
    	
    	$this->db->query($sql); 
    }
    
    function del_property($proid){
    	$this->load->database();
    	$sql="DELETE FROM property
    		  WHERE property_id='".$proid."'";
    	return $this->db->query($sql);
    }
    
    function del_property_pic($proid){
    	$this->load->database();
    	$sql="DELETE FROM pic_id_property
    		  WHERE property_id='".$proid."'";
    	return $this->db->query($sql);
    }
    
    function get_property_id($id) {
    	$this->load->database();
    		$sql = "SELECT DISTINCT property.property_id,
						property.property_name,
						(SELECT location_name FROM loc_property loc WHERE loc.location_id = property.location_id  LIMIT 1) as location_name,
						property.property_type,
						property.no_of_floor,
						property.no_of_bedrooms,
						property.features,
						property.description,
						property.property_price,
						property.b_size,
						property.date_create,
						property.land_size,
						property.file_path,
						property.user_upload,
						property.parking,
						(SELECT pic_name FROM pic_id_property pi WHERE pi.property_id = property.property_id  LIMIT 1) as pic_name,
						property.no_of_bathroom,
						property.location_id,
						property.property_cat
				FROM property					
				WHERE 	property.property_id=".$id."
				ORDER BY property.date_create DESC";
    		$query= $this->db->query($sql);
			$nb_rows = $query->num_rows();
	        if ($nb_rows < 1) {
	            return NULL;
	        } else {
	        return $query->row_array(); 
	        }
    }
    function get_pic_id($proid){
    	$this->load->database();
    	$sql="SELECT property_id,
    				 pic_name
    		  FROM pic_id_property
    		  WHERE property_id=".$proid."";
    	return $this->db->query($sql);
    }
    
    function del_pic($pic){
    	$this->load->database();
    	$sql="DELETE FROM pic_id_property
    		  WHERE pic_name='".$pic."'";
    	return $this->db->query($sql);
    }
     
    function get_loc_pro(){
    	$this->load->database();
    	$sql="SELECT location_id,
    				 location_name,
    				 parent_id
    		  FROM loc_property";
    	return $this->db->query($sql);
    }
    
    function insert_pic_by_id($prop_id,$pic_name){
    	$this->load->database();
    	$sql="INSERT INTO pic_id_property(property_id, pic_name)
    		  VALUES(".$prop_id.",
					 '".pg_escape_string($pic_name)."')";
		$this->db->query($sql); 
	}
	
    function property_category(){
    	$sql="SELECT prop_cat_id, 
    	             prop_cat_name 
    	        FROM property_category
    	        ORDER BY prop_cat_name";
		return $this->db->query($sql); 
	}
    
	function get_last_id(){
		$this->load->database();
		$sql="SELECT property_id FROM property order by property_id desc LIMIT 1" ;
		$query= $this->db->query($sql);
		$nb_rows = $query->num_rows();
        if ($nb_rows < 1) {
            return NULL;
        } else {
        return $query->row_array(); 
        }
	}
	
    function q(){
    	$this->load->database();
    	return $this->db->query($sql);
    }
   
    
    function get_result($criteria,$num,$offset=0) {
    	
    	//$split_str = explode(" ",strtolower());
    	$sql="SELECT DISTINCT property.property_id,
							property.property_name,
							property.property_type,
							property.no_of_floor,
							property.no_of_bedrooms,
							property.features,
							property.description,
							property.property_price,
							property.b_size,
							property.date_create,
							property.land_size,
							property.file_path,
							property.user_upload,
							property.parking,
						    (SELECT pic_name FROM pic_id_property pi WHERE pi.property_id = property.property_id  LIMIT 1) as pic_name,
							property.no_of_bathroom,
							loc_property.location_name
					FROM property, loc_property
    	 			WHERE loc_property.location_id = property.location_id "; 
    	 $where = "";
    	 if ($criteria["loc"] != "") {
    	      $where .= " AND property.location_id = ".$criteria["loc"];	
    	 }
    	 
    	 if ($criteria["ref"] != "" && $criteria["ref"] != "House / Building name") {
    	 	 $where .= "AND property.property_name LIKE '%".pg_escape_string($criteria["ref"])."%'";
    	 }
    	 
    	 if ($criteria["type"] == "sale" || $criteria["type"] == "rent") {
    	 	$where .= "AND property.property_type = '".pg_escape_string($criteria["type"])."'";
    	 }
    	 
    	 if ($criteria["cat"] != "") {
    	 	$where .= "AND property.property_cat = ".$criteria["cat"];
    	 }
    	 
    	 if ($criteria["price_min"] != "" && is_numeric($criteria["price_min"])) {
    	 	 $where .= "AND property.property_price >= ".$criteria["price_min"];
    	 }
    	 
    	 if ($criteria["price_max"] != "" && is_numeric($criteria["price_max"])) {
    	 	 $where .= "AND property.property_price <= ".$criteria["price_max"];
    	 }
    	 
    	 if ($offset != ""){
    	 	$limit = " LIMIT ".$num." OFFSET ".$offset;
    	 	
    	 }elseif($num!=""){
    	 	$limit = " LIMIT ".$num." OFFSET 0";
    	 	
    	 }else{ $limit="";}
    	
    	 $sql .= $where;
    	 $sql .= " ORDER BY date_create DESC".$limit;
					
		return $this->db->query($sql);
    }
    
	function get_property_hit($id){
		$this->load->database();
		$sql="SELECT property_id
				FROM property_hits 
				WHERE property_id=".$id;
		return $this->db->query($sql);
	}
	
	function insert_property_hit($id){
		$this->load->database();
		$sql="INSERT INTO property_hits(property_id) VALUES(".$id.")";
		$this->db->query($sql);
	}
	
	function getUser_id($user_id){
		$this->load->database();
		$sql="SELECT user_upload
				FROM property 
				WHERE user_upload=".$user_id;
		return $this->db->query($sql);
	}
	
	function get_user_id($user_id){
		    $this->load->database();
            $sql="SELECT user_id,user_fname,user_lname,user_login,user_email,user_pwd,user_tel,user_photo,date_create,date_update
   				  FROM users 
   				  WHERE user_id=".$user_id;
			return $this->db->query($sql);
			//return $query->result_array();
		
	}	


}
    
?>